package database;

import generator.Ramka;
import java.sql.*;
import java.util.Properties;



/**
 *
 * @author A
 */
public class ZaladowanieDanychDoBazyZexcela {
  private String custom[][];
  private String product[][];
   private Integer CustomCounter=0;
   private Integer ProductCounter=0;
    public ZaladowanieDanychDoBazyZexcela() {
         

        
        
    Properties prop = new Properties();
    prop.put("charSet", "windows-1250"); 
    prop.put("user", "");
    prop.put("password", "");
        
     try{

                    
    String myDB = "jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=.\\Baza_Klientow.xls;"
        + "DriverID=22;READONLY=false";
        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        Connection con = DriverManager.getConnection(myDB,prop);
        Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
        ResultSet rs = stmt.executeQuery("select * from [Arkusz2$]");

        while(rs.next()){
           CustomCounter++;
        }
        
        custom = new String[CustomCounter][5];  
       
      
        rs.beforeFirst();
        CustomCounter=0;
        
            while(rs.next()){

                custom[CustomCounter][0]=rs.getString(3);
                custom[CustomCounter][1]=rs.getString(2); 
                custom[CustomCounter][2]=rs.getString(4); 
                 custom[CustomCounter][3]=rs.getString(5); 
                 custom[CustomCounter][4]=rs.getString(6); 
                 CustomCounter++;
         
            }
            
            Ramka.setCustomerRownames(custom);
      //      Ramka ram = new Ramka();
       //     ram.setVisible(true);
                        

            rs.close();
            stmt.close();
            con.close();
        }
        catch(ClassNotFoundException | SQLException e){
            e.printStackTrace();
        }
     
     
     // Produkty
     
     try{
        String myDB = "jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=.\\Produkty.xls;"
        + "DriverID=22;READONLY=false";
        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        Connection con = DriverManager.getConnection(myDB,prop);
        Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
        ResultSet rs = stmt.executeQuery("select * from [Pozycje$]");
        
        while(rs.next()){
           ProductCounter++;
        }
       product = new String[ProductCounter][4];
        rs.beforeFirst();
        ProductCounter=0;
        
        while(rs.next()){
            product[ProductCounter][0]=rs.getString(1);
            product[ProductCounter][1]=rs.getString(2);
            product[ProductCounter][2]=rs.getString(3);
            product[ProductCounter][3]=rs.getString(4);
            ProductCounter++;
        }
        
        Ramka.setProductRownames(product);
              Ramka ram = new Ramka();
                ram.setVisible(true);
        
        
            rs.close();
            stmt.close();
            con.close();
     }
         catch(ClassNotFoundException | SQLException e){
         e.printStackTrace();
        }

  }
    



}
